﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Diagnostics;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Windows.Forms;

namespace Filmek
{
    /// <summary>
    /// Summary description for DAL.
    /// </summary>
    public abstract class DAL2
    {
        protected static bool kapcsolodva;
        protected static bool ConnectionCreated;
        protected static SqlConnection myAccessConn;

        //protected string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\Autokolcsonzo.MDB"; 
        //protected string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Programs\\SQL\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\Filmek.mdf";
        protected string strAccessConn = "Data Source=(local); Initial Catalog=FilmekUj; User Id=Peter; Integrated Security=true";
        protected SqlDataReader myDataReader;
        protected SqlCommand myComm;
        protected System.Data.DataSet myDataSet;

        protected bool IsConnectCreated()
        {
            return ConnectionCreated;
        }
        protected bool IsConnected()
        {
            return kapcsolodva;
        }

        protected SqlConnection theConnection()
        { return myAccessConn; }

        protected SqlDataReader ExecuteReader(string query, ref string errorMessage)
        {
            try
            {
                OpenConnection();
                SqlCommand cmd = new SqlCommand(query, myAccessConn);
                SqlDataReader rdr = cmd.ExecuteReader();
                errorMessage = "OK";
                return rdr;

            }
            catch (SqlException e)
            {
                errorMessage = e.Message;
                CloseConnection();
                return null;
            }
        }

        protected bool MakeConnection()
        {
            // Create the Connection if is was not already created.
            if (ConnectionCreated != true)
            {
                try
                {
                    myAccessConn = new SqlConnection(strAccessConn);
                    myAccessConn.Open();
                    ConnectionCreated = true;
                    myAccessConn.Close();
                    kapcsolodva = false;
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else { return true; }
        }

        protected SqlDataReader myExecuteReader(string sQuery, ref string ErrMess)
        {
            try
            {
				//CloseConnection();
                this.OpenConnection();
                myComm = new SqlCommand(sQuery, myAccessConn);
                myDataReader = myComm.ExecuteReader();
                ErrMess = "OK";
                //			this.CloseConnection();
                return myDataReader;

            }
            catch (SqlException e)
            {
                ErrMess = e.Message;
                this.CloseConnection();
                return null;
            }
        }
        /// <summary>
        /// Open the Connection when the state is not already open.
        /// </summary>
        protected bool OpenConnection()
        {
            // Open the Connection when the state is not already open.
            if (kapcsolodva != true)
            {
                try
                {
                    myAccessConn.Open();
                    kapcsolodva = true;
                    return true;
                }
                catch
                {
                    return false;
                }

            }
            else { return true; }
        }

        internal void CloseConnection()
        {
            // Close the Connection when the connection is opened.
            if (kapcsolodva == true)
            {
                myAccessConn.Close();
                kapcsolodva = false;
            }
        }

        protected int myExecuteScalar(string myQuery, ref string ErrMess)
        {
            int iScalar = 0;
            this.OpenConnection();
            myComm = new SqlCommand(myQuery, myAccessConn);
            try
            {
                iScalar = Convert.ToInt32(myComm.ExecuteScalar());
            }
            catch (Exception e)
            {
                ErrMess = e.Message;

            }
            this.CloseConnection();
            return iScalar;

        }
        protected DataSet myFillDataSetFromString(String sSQL, String sTableName, ref SqlDataAdapter da, ref string Errm)
        {
            //da = new SqlDataAdapter();
            try
            {
                //		da.TableMappings.Add("Table", sTableName);
                OpenConnection();
                myComm = new SqlCommand(sSQL, myAccessConn);
                myComm.CommandType = CommandType.Text;
                da.SelectCommand = myComm;
                DataSet ds = new DataSet(sTableName);
                da.Fill(ds);
                CloseConnection();
                Errm = "OK";
                return ds;
            }
            catch (Exception ex)
            {
                Errm = ex.Message;
                return null;
            }
        }
        protected string myFillExistingDataSet(String sSQL, String sTableName, ref SqlDataAdapter da, ref DataSet ds)
        {
            //da = new SqlDataAdapter();
            String Errm;
            try
            {
                da.TableMappings.Add("Table", sTableName);
                OpenConnection();
                myComm = new SqlCommand(sSQL, myAccessConn);
                myComm.CommandType = CommandType.Text;
                da.SelectCommand = myComm;
                //DataSet ds= new DataSet(sTableName);
                da.Fill(ds);
                CloseConnection();
                Errm = "OK";
                return Errm;
            }
            catch (Exception ex)
            {
                Errm = ex.Message;
                return Errm;
            }
        }

        protected void myInsertCommand(string sInsert, SqlDataAdapter da, ref string Errm)
        {
            try
            {
                OpenConnection();
                da.InsertCommand = new SqlCommand(sInsert, myAccessConn);
                da.InsertCommand.ExecuteNonQuery();
                CloseConnection();
                Errm = "OK";
            }
            catch (Exception ex)
            {
                Errm = ex.ToString();
            }
            CloseConnection();
        }

        protected void MyDeleteComm(string sDelete, SqlDataAdapter da, ref string Err)
        {
            try
            {
                OpenConnection();
                da.DeleteCommand = new SqlCommand(sDelete, myAccessConn);
                da.DeleteCommand.ExecuteNonQuery();
                Err = "OK";
            }
            catch (Exception ex)
            {
                Err = ex.ToString();
            }
            CloseConnection();
        }

        public DataTable QueryCmd(String sqlCmd)
        {
            try
            {
                myAccessConn.Close();
                myAccessConn.Open();
                this.OpenConnection();
                SqlCommand MyCmd = new SqlCommand(sqlCmd, myAccessConn);
                SqlDataReader dr = MyCmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception es) { throw (es); }
            finally { this.CloseConnection(); }
        }

		protected void MyExecuteNonQueryWithParameters(String cmd, List<String> parameterNames, List<String> parameterValue)
		{
			// Parameterrekkel elallatott tartol eljarasok futattasa
			try
			{
				this.OpenConnection();
				myComm = new SqlCommand(cmd, myAccessConn);
				List<SqlParameter> param = new List<SqlParameter>();
				SqlParameter p;
				for (int i = 0; i < parameterNames.Count; i++)
				{
					p = new SqlParameter();
					p.ParameterName = parameterNames[i];
					p.Value = parameterValue[i];
					param.Add(p);
				}
				for (int i = 0; i < param.Count; i++)
				{
					myComm.Parameters.Add(param[i]);
				}
				myComm.ExecuteNonQuery();
				myComm.Dispose();
				this.CloseConnection();
			}
			catch (Exception e)
			{
				MessageBox.Show("Hiba a parameteres eljaras vegrehajtasa kozben!" + e.Message);
				this.CloseConnection();
			}
		}
    }

}




